package dao;

import bean.Parametrizacao;
import bean.Tarefa;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class TarefaDAO {

    private Connection con;
    SimpleDateFormat data = new SimpleDateFormat("dd/MM/yyyy");
    int prm ;


    public TarefaDAO( ){
        try {
            this.con = new ConnectionFactory().getConnection();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(TarefaDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

     public void salvar(Tarefa tarefas) {

        String insert = "insert into tarefas ( descricao, data_tarefa, hora_inicio, hora_fim, duracao, minutos ) values (  ?, ?, ?, ?, ?, ? )";
        try {
            prm = 0;
            PreparedStatement stmt = this.con.prepareStatement(insert);
            Statement s = con.createStatement( );
            stmt.setString(++prm, tarefas.getDescricao  ( ) );
            stmt.setDate  (++prm, tarefas.getData_tarefa( ) );
            stmt.setString(++prm, tarefas.getHora_inicio( ) );
            stmt.setString(++prm, tarefas.getHora_fim   ( ) );
            stmt.setString(++prm, tarefas.getDuracao    ( ) );
            stmt.setInt   (++prm, tarefas.getMinutos    ( ) );
            
            stmt.execute();
            s.execute("SHUTDOWN");
            stmt.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
     public void editar(Tarefa tarefas) {

        String insert = "update tarefas set "
                      + "descricao = ?, "
                      + "data_tarefa = ?, "
                      + "hora_inicio = ?, "
                      + "hora_fim = ?, "
                      + "duracao = ?, "
                      + "minutos = ? "
                      + "where id = ?";
        try {
            prm = 0;
            PreparedStatement stmt = this.con.prepareStatement(insert);
            Statement s = con.createStatement( );
            stmt.setString(++prm, tarefas.getDescricao  ( ) );
            stmt.setDate  (++prm, tarefas.getData_tarefa( ) );
            stmt.setString(++prm, tarefas.getHora_inicio( ) );
            stmt.setString(++prm, tarefas.getHora_fim   ( ) );
            stmt.setString(++prm, tarefas.getDuracao    ( ) );
            stmt.setInt   (++prm, tarefas.getMinutos    ( ) );
            stmt.setInt   (++prm, tarefas.getId         ( ) );
            stmt.execute();
            s.execute("SHUTDOWN");
            stmt.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
               
     public void deletar(Tarefa tarefas) {
        String insert = "delete from tarefas where id = ?";
        try {
            PreparedStatement stmt = this.con.prepareStatement(insert);
            stmt.setInt(1, tarefas.getId( ) );
            stmt.execute( );
            stmt.close( );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Tarefa> getLista() throws SQLException {
        String sql = "select * from tarefas where data_tarefa = ? ";
        
        java.util.Date dataUtil = new java.util.Date();  
        java.sql.Date dataSql = new java.sql.Date(dataUtil.getTime());  
        
        try {
            List<Tarefa> lista_contatos = new ArrayList<Tarefa>( );
            PreparedStatement stmt = this.con.prepareStatement( sql );
            stmt.setDate(1, dataSql );
            ResultSet rs = stmt.executeQuery( );
            
            while ( rs.next( ) )
            {
                Tarefa tarefa = new Tarefa( );
                tarefa.setId         ( rs.getInt   ( "id"          ) );
                tarefa.setDescricao  ( rs.getString( "descricao"   ) );
                tarefa.setData_tarefa( rs.getDate( "data_tarefa" ) );
                tarefa.setHora_fim   ( rs.getString( "hora_fim"    ) );
                tarefa.setHora_inicio( rs.getString( "hora_inicio" ) );
                tarefa.setDuracao    ( rs.getString( "duracao"     ) );
                tarefa.setMinutos    ( rs.getInt   ( "minutos"     ) );
                lista_contatos.add(tarefa);
            }
            rs.close();
            stmt.close();
            
            return lista_contatos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
   
    public List<Tarefa> buscaData(java.util.Date p_date) throws SQLException {
        
        
        java.sql.Date dataSql = new java.sql.Date(p_date.getTime());  
        
        String sql = "select * from tarefas where data_tarefa = ? ";
        try {
            List<Tarefa> lista_contatos = new ArrayList<Tarefa>( );
            PreparedStatement stmt = this.con.prepareStatement( sql );
            stmt.setDate( 1, dataSql);
            ResultSet rs = stmt.executeQuery( );
            
            while ( rs.next( ) )
            {
                Tarefa tarefa = new Tarefa( );
                tarefa.setId         ( rs.getInt   ( "id"          ) );
                tarefa.setDescricao  ( rs.getString( "descricao"   ) );
                tarefa.setData_tarefa( rs.getDate( "data_tarefa" ) );
                tarefa.setHora_fim   ( rs.getString( "hora_fim"    ) );
                tarefa.setHora_inicio( rs.getString( "hora_inicio" ) );
                tarefa.setDuracao    ( rs.getString( "duracao"     ) );
                tarefa.setMinutos    ( rs.getInt   ( "minutos"     ) );
                lista_contatos.add(tarefa);
            }
            rs.close();
            stmt.close();
            
            return lista_contatos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
        
    
     public List<Tarefa> somaPorPeriodo(Date dt_ini, Date dt_fim) throws SQLException {
        String sql = "select data_tarefa, sum(minutos) as somamin from tarefas Where data_tarefa >= ? and data_tarefa <= ? group by data_tarefa order by data_tarefa desc";
        try {
            List<Tarefa> lista_contatos = new ArrayList<Tarefa>( );
            PreparedStatement stmt = this.con.prepareStatement( sql );
            stmt.setDate( 1, dt_ini );
            stmt.setDate( 2, dt_fim );
            ResultSet rs = stmt.executeQuery( );
            
            while ( rs.next( ) )
            {
                Tarefa tarefa = new Tarefa( );
          
                tarefa.setData_tarefa( rs.getDate( "data_tarefa" ) );
                tarefa.setMinutos    ( rs.getInt ( "somamin"     ) );
                lista_contatos.add(tarefa);
            }
            rs.close();
            stmt.close();
            
            return lista_contatos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
        
    public List<Tarefa> buscaDescricao(String str) throws SQLException {
        String sql = "select * from tarefas where upper(descricao) like upper('%"+str+"%') limit 100";
        try {
            List<Tarefa> lista_contatos = new ArrayList<Tarefa>( );
            PreparedStatement stmt = this.con.prepareStatement( sql );
            ResultSet rs = stmt.executeQuery( );
            
            while ( rs.next( ) )
            {
                Tarefa tarefa = new Tarefa( );
                tarefa.setId         ( rs.getInt   ( "id"          ) );
                tarefa.setDescricao  ( rs.getString( "descricao"   ) );
                tarefa.setData_tarefa( rs.getDate( "data_tarefa" ) );
                tarefa.setHora_fim   ( rs.getString( "hora_fim"    ) );
                tarefa.setHora_inicio( rs.getString( "hora_inicio" ) );
                tarefa.setDuracao    ( rs.getString( "duracao"     ) );
                tarefa.setMinutos    ( rs.getInt   ( "minutos"     ) );
                lista_contatos.add(tarefa);
            }
            rs.close();
            stmt.close();
            
            return lista_contatos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    public String buscaUltimo( ) throws SQLException {
        String sql = "select descricao from tarefas order by data_tarefa desc limit 1";
        String descricao = "";
        try {
            PreparedStatement stmt = this.con.prepareStatement( sql );
            ResultSet rs = stmt.executeQuery( );            
            while ( rs.next( ) ){
            	 descricao = rs.getString("descricao");            	
            }            
            rs.close();
            stmt.close();
            return descricao;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    public List<Tarefa> juntarTarefas(String str, Date data) throws SQLException {
        String sql = "select * from tarefas where descricao = ? and data_tarefa = ? ";
        try {
            List<Tarefa> lista_contatos = new ArrayList<Tarefa>( );
            PreparedStatement stmt = this.con.prepareStatement( sql );
            stmt.setString( 1, str );
            stmt.setDate( 2, data );
            ResultSet rs = stmt.executeQuery( );
            
            while ( rs.next( ) )
            {
                Tarefa tarefa = new Tarefa( );
                tarefa.setId         ( rs.getInt   ( "id"          ) );
                tarefa.setDescricao  ( rs.getString( "descricao"   ) );
                tarefa.setData_tarefa( rs.getDate( "data_tarefa" ) );
                tarefa.setHora_fim   ( rs.getString( "hora_fim"    ) );
                tarefa.setHora_inicio( rs.getString( "hora_inicio" ) );
                tarefa.setDuracao    ( rs.getString( "duracao"     ) );
                tarefa.setMinutos    ( rs.getInt   ( "minutos"     ) );
                lista_contatos.add(tarefa);
            }
            rs.close();
            stmt.close();
            
            return lista_contatos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    public void updatePrm( Parametrizacao param ) {

            String sql = "update parametrizacao set "
                        + "valor = ? "
                        + "where parametro = ?";
            try {
                    Connection conn;
                    conn = new ConnectionFactory().getConnection();
                prm = 0;
                PreparedStatement stmt = conn.prepareStatement(sql);
                Statement s = conn.createStatement( );
                stmt.setString(++prm, param.getValor( ) );
                stmt.setString(++prm, param.getParametro( ) );
                stmt.execute();
                s.execute("SHUTDOWN");
                stmt.close();

            } catch (SQLException e) {
                throw new RuntimeException(e);
            } catch (ClassNotFoundException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                    }
        }

    public HashMap<String,String> getPrm( ) throws SQLException {
            String sql = "select * from parametrizacao";
            String parametro = "";
            String valor = "";
            try {
                    HashMap<String,String> mapa = new HashMap<String, String>();
                PreparedStatement stmt = this.con.prepareStatement( sql );
                ResultSet rs = stmt.executeQuery( );

                while ( rs.next( ) ){
                    valor     = rs.getString( "valor"     );   
                    parametro = rs.getString( "parametro" ) ;  
                    mapa.put(parametro, valor);
                }
                rs.close();
                stmt.close();

                return mapa;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
         
}
